﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DreamIn.Structures.LocalDB.entities;
using System.Data.SqlServerCe;

namespace DreamIn.Structures.LocalDB.DataAccessLayer
{
    class AlbumDAL
    {
        public List<AlbumE> GetAlbums()
        {
            SqlCeConnection conn = DALHelper.Connection;
            try{
                SqlCeCommand cmd = new SqlCeCommand("select id_album, albumName, albumPath, albumFolderName from album", conn);
                List<AlbumE> result = new List<AlbumE>();

                conn.Open();
                SqlCeDataReader reader= cmd.ExecuteReader();

                while (reader.Read())
                {
                    result.Add(new AlbumE()
                    {
                        albumID = (int)(reader[0]),
                        albumName = (string)reader[1],
                        albumPath = (string)reader[2],
                        albumFolderName = (string)reader[3]
                    });
                }
                reader.Close();
                return result;
            }
            finally
            {
                conn.Close();
            }
        }

        public void AddAlbum(string name, string path, string folderName)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("insert into album (albumName, albumPath, albumFolderName) values ('" + name + "', '" + path + "', '" + folderName + "')", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }


        public void DeleteAlbum(AlbumE album)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("delete from album where id_album='"+album.albumID+"'", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void UpdateAlbum(AlbumE album)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("update album set albumName='" + album.albumName + "', albumPath='"+album.albumPath+"', albumFolderName='"+album.albumFolderName+"' where id_album='"+album.albumID+"'", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

    }
}
